package com.genghis.ptas.statistic.dao.impl;

import com.genghis.core.page.dao.PageDao;
import com.genghis.ptas.statistic.dao.StatisticDao;
import com.genghis.ptas.statistic.entity.CourseStatistics;
import com.genghis.ptas.statistic.entity.Statistic;
import com.genghis.ptas.statistic.entity.TypeStatistic;
import com.genghis.ptas.util.ExtremeValue;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.stereotype.Repository;

import java.util.List;

/**
 * Created with IntelliJ IDEA.
 * User: gaoyang
 * To change this template use File | Settings | File Templates.
 */
@Repository("statisticDao")
public class StatisticDaoImpl extends PageDao implements StatisticDao {

    @Override
    public List<TypeStatistic> statisticRegisterType(String ids) {
        StringBuilder sql = new StringBuilder("SELECT * FROM ( \n" +
                "SELECT t.type_name showType, \n" +
                "IFNULL(SUM(s.collect_number),0) collect_number, \n" +
                "IFNULL(SUM(s.register_number),0) register_number ,\n" +
                "IFNULL(SUM(s.total_number),0) total_number FROM type_values t LEFT JOIN \n" +
                "(SELECT cs.*,c.course_type FROM course_statistics cs,course c \n" +
                "WHERE cs.course_id=c.id) s \n" +
                "ON t.id= s.course_type GROUP BY t.`type_name`) r  ");
//        if (!"".equals(ids)) {
//            sql.append("AND r.typeId IN(").append(ids).append(")");
//        }
        return getJdbcTemplate().query(sql.toString(), new BeanPropertyRowMapper<TypeStatistic>(TypeStatistic.class));
    }

    @Override
    public List<TypeStatistic> statisticTotalCourse(String ids) {
        StringBuilder sql = new StringBuilder("SELECT COUNT(*) courseNum , t.type_name showType \n" +
                "FROM type_values t LEFT JOIN course c ON t.id=c.course_type GROUP BY t.`type_name` ");
        if (!"".equals(ids)) {
            sql.append("AND t.id IN (").append(ids).append(")");
        }
        return getJdbcTemplate().query(sql.toString(), new BeanPropertyRowMapper<TypeStatistic>(TypeStatistic.class));
    }

    @Override
    public List<CourseStatistics> statisticCourse(CourseStatistics courseStatistic) {
        StringBuilder sql = new StringBuilder("SELECT cs.*, ");
        sql.append("cs.").append(courseStatistic.getStatisticItem()).append(" showNum ");
        getStatisticSql(courseStatistic, sql);
        return getJdbcTemplate().query(sql.toString(), new BeanPropertyRowMapper<CourseStatistics>(CourseStatistics.class));
    }

    private void getStatisticSql(CourseStatistics courseStatistic, StringBuilder sql) {
        sql.append("FROM course_statistics cs, course c WHERE cs.course_id=c.id ");
        if (!"".equals(courseStatistic.getTypeId()) && null != courseStatistic.getTypeId()) {
            sql.append("AND c.`course_type`IN( ").append(courseStatistic.getTypeId()).append(" ) ");
        }
        if (!"".equals(courseStatistic.getStatisticItem()) && null != courseStatistic.getStatisticItem()) {
            sql.append("ORDER BY cs.").append(courseStatistic.getStatisticItem()).append(" ").append(courseStatistic.getSort());
        }
        if (!"".equals(courseStatistic.getRankNum()) && null != courseStatistic.getRankNum()) {
            sql.append(" LIMIT ").append(courseStatistic.getRankNum());
        }
    }

    @Override
    public ExtremeValue statisticCourseExtremeValue(CourseStatistics courseStatistic) {
        StringBuilder sql = new StringBuilder("");
        sql.append("SELECT MAX(").append(courseStatistic.getStatisticItem()).append(") upper, ");
        sql.append("MIN(").append(courseStatistic.getStatisticItem()).append(") lower, ");
        sql.append(courseStatistic.getRankNum()).append(" rankNum ");
        getStatisticSql(courseStatistic, sql);
        return getJdbcTemplate().queryForObject(sql.toString(), new BeanPropertyRowMapper<ExtremeValue>(ExtremeValue.class));
    }

    @Override
    public List<TypeStatistic> statisticDetail(String typeName) {
        String sql = "SELECT * FROM ( \n" +
                "SELECT t.type_value showType,IFNULL(SUM(s.collect_number),0) collect_number, \n" +
                "IFNULL(SUM(s.register_number),0) register_number ,\n" +
                "IFNULL(SUM(s.total_number),0) total_number, t.id typeId  FROM (\n" +
                "SELECT *\n" +
                " FROM type_values t WHERE t.`type_name`='" + typeName + "') t LEFT JOIN \n" +
                "(SELECT cs.*,c.course_type FROM course_statistics cs,course c \n" +
                "WHERE cs.course_id=c.id) s\n" +
                "ON t.id= s.course_type GROUP BY t.id) r ";
        return getJdbcTemplate().query(sql, new BeanPropertyRowMapper<TypeStatistic>(TypeStatistic.class));
    }

    @Override
    public List<TypeStatistic> detailResource(String typeName) {
        String sql = "SELECT COUNT(*) courseNum,t.type_value showType  FROM course c,type_values t \n" +
                "WHERE c.`course_type`=t.`id` AND t.`type_name`='" + typeName + "' GROUP BY t.`type_value`  ";
        return getJdbcTemplate().query(sql, new BeanPropertyRowMapper<TypeStatistic>(TypeStatistic.class));
    }

    @Override
    public List<CourseStatistics> analysisCourse(String ids) {
        String sql = "SELECT c.`course_name`,cs.* FROM `course_statistics` cs, `course` c WHERE cs.`course_id`=c.`id` AND cs.`id` IN (" + ids + ")";
        return getJdbcTemplate().query(sql, new BeanPropertyRowMapper<CourseStatistics>(CourseStatistics.class));
    }

    @Override
    public List<Statistic> analysisUser(String ids) {
        String sql = "SELECT * FROM (\n" +
                "SELECT re.*,co.collectNumber FROM (\n" +
                "SELECT u.id userId, u.user_name, COUNT(*) registerNumber FROM `sys_user` u LEFT JOIN `course_register` c ON u.`id`=c.`student_id` GROUP BY u.`id`\n" +
                ") re LEFT JOIN (\n" +
                "SELECT COUNT(*) collectNumber,u.id userId FROM `sys_user` u LEFT JOIN `course_collect` c ON u.`id`=c.`user_id` GROUP BY u.`id`\n" +
                ") co ON re.userId=co.userId )r\n" +
                "WHERE r.userId IN (" + ids + ")";
        return getJdbcTemplate().query(sql, new BeanPropertyRowMapper<Statistic>(Statistic.class));
    }

    @Override
    public List<TypeStatistic> analysisUserLike(String userId) {
        String sql = "SELECT m.id,m.type_value showType,m.register_number,n.collect_number FROM (\n" +
                "SELECT t.*, IFNULL(countRegister,0) register_number FROM type_values t LEFT JOIN (\n" +
                "SELECT c.course_type,COUNT(*) countRegister FROM course_register cr,course c WHERE cr.course_id=c.id AND cr.student_id=" + userId + " GROUP BY course_type\n" +
                ") c ON t.id=c.course_type\n" +
                ") m LEFT JOIN (\n" +
                "SELECT t.*, IFNULL(countCollect,0) collect_number FROM type_values t LEFT JOIN (\n" +
                "SELECT c.course_type,COUNT(*) countCollect FROM course_collect cr,course c WHERE cr.course_id=c.id AND cr.user_id=" + userId + " GROUP BY course_type\n" +
                ") c ON t.id=c.course_type) n ON m.id=n.id ";
        return getJdbcTemplate().query(sql, new BeanPropertyRowMapper<TypeStatistic>(TypeStatistic.class));
    }

}
